# -*- coding: utf-8 -*-

from json import load
import pymysql
import pymysql.cursors
from datetime import datetime, time
import pandas as pd
from dotenv import load_dotenv
import os
from sqlalchemy import create_engine  # 新增导入
from pathlib import Path
import sys

# 获取项目根目录，假设当前文件所在路径结构固定
project_root = Path(__file__).resolve().parent.parent.parent
print(f"项目根目录: {project_root}")
# # 将项目根目录添加到系统路径
sys.path.append(str(project_root))
from entity.daily_attendance import DailyAttendance


def connect_db():
    load_dotenv("./.env")
    db_host = os.getenv("MYSQL_HOST")
    db_user = os.getenv("MYSQL_USER")
    db_password = os.getenv("MYSQL_PASSWORD")
    db_name = os.getenv("MYSQL_DBNAME")
    db_port = int(os.getenv("MYSQL_PORT", "3306"))

    try:
        connection = pymysql.connect(
            host=db_host,
            port=db_port,
            user=db_user,
            passwd=db_password,
            database=db_name,
            charset="utf8",
            cursorclass=pymysql.cursors.DictCursor,
        )
        print(f"成功连接到数据库 {db_name}")
        return connection
    except pymysql.Error as e:
        print(f"连接数据库失败: {e}")
        return None


def fetch_attendance_records(connection, start_date_str, end_date_str):
    """获取指定日期范围内的出勤记录并返回 Pandas DataFrame"""
    query = """
        SELECT ter.card_no,tei.name,ter.entry_time
        FROM t_entry_record ter
        LEFT JOIN t_employee_info tei
        ON ter.card_no = tei.card_no
        WHERE DATE(entry_time) >= %s AND DATE(entry_time) <= %s
        ORDER BY card_no, entry_time
    """
    try:
        # 使用cursor执行查询
        with connection.cursor() as cursor:
            cursor.execute(query, (start_date_str, end_date_str))
            result = cursor.fetchall()
            df = pd.DataFrame(result)

        if df.empty:
            return pd.DataFrame()
        # 确保 entry_time 是 datetime 对象
        df["entry_time"] = pd.to_datetime(df["entry_time"])
        return df
    except pymysql.Error as e:
        print(f"查询数据失败: {e}")
        return pd.DataFrame()
    except Exception as e:
        print(f"处理数据时发生未知错误: {e}")
        return pd.DataFrame()


def analyze_records(df_records):
    """使用 Pandas 分析出勤记录并统计状态"""
    if df_records.empty:
        return []

    # 规则时间
    normal_arrival_start = time(6, 0, 0)
    normal_arrival_end = time(8, 40, 0)
    normal_departure_time = time(17, 30, 0)

    # 提取日期和时间部分
    df_records["entry_date"] = df_records["entry_time"].dt.date
    df_records["entry_clock_time"] = df_records["entry_time"].dt.time

    # 按工号和日期分组，计算最早和最晚打卡时间
    daily_summary = df_records.groupby(
        ["card_no", "name", "entry_date"]
    )  # 添加name到分组
    daily_summary = daily_summary["entry_clock_time"].agg(["min", "max"]).reset_index()
    daily_summary.rename(
        columns={"min": "arrival_time", "max": "departure_time"}, inplace=True
    )

    daily_attendance = []

    for _, row in daily_summary.iterrows():
        card_no = row["card_no"]
        name = row["name"]
        date_obj = row["entry_date"]
        arrival_time = row["arrival_time"]
        departure_time = row["departure_time"]

        # 判断上班状态
        if arrival_time > normal_arrival_end:
            arrival_status = "迟到"
        elif arrival_time < normal_arrival_start:
            arrival_status = "过早"
        else:
            arrival_status = "正常"

        # 判断下班状态
        if arrival_time == departure_time:
            departure_status = "可能漏打卡"
        elif departure_time < normal_departure_time:
            departure_status = "早退"
        else:
            departure_status = "正常"

        daily_attendance.append(
            DailyAttendance(
                name=row.get("name", ""),  # 确保传递name参数
                card_no=card_no,
                date=date_obj.isoformat(),
                arrival_status=arrival_status,
                arrival_time=arrival_time.strftime("%H:%M:%S"),
                departure_status=departure_status,
                departure_time=departure_time.strftime("%H:%M:%S"),
            )
        )

    return daily_attendance


def format_attendance_summary(attendance_summary, start_date_str, end_date_str):
    """格式化出勤统计结果为字符串列表"""
    output_lines = []
    if not attendance_summary:
        output_lines.append("没有找到符合条件的记录或分析结果为空。")
        return output_lines

    output_lines.append(f"--- {start_date_str} 到 {end_date_str} 出勤统计 ---")

    # 按工号分组
    grouped = {}
    for record in attendance_summary:
        if record.card_no not in grouped:
            grouped[record.card_no] = []
        grouped[record.card_no].append(record)

    # 按日期排序后输出
    for card_no, records in grouped.items():
        output_lines.append(f"\n工号: {card_no}")
        for record in sorted(records, key=lambda x: x.date):
            output_lines.append(str(record))

    return output_lines


def fetch_and_analysis(start_date_str, end_date_str):
    ret_df = (
        pd.DataFrame()
    )  # 初始化一个空的DataFrame，但后续不会直接使用这个空DataFrame
    connection = connect_db()

    if connection:
        try:
            records_df = fetch_attendance_records(
                connection, start_date_str, end_date_str
            )
            if records_df.empty:
                return (
                    ret_df.copy()
                )  # 返回空DataFrame的一个副本（虽然这里直接返回ret_df也可以）

            attendance_summary = analyze_records(records_df)

            # 使用列表推导式创建字典列表，然后将其转换为DataFrame并赋值给ret_df
            # 但实际上，我们直接返回这个新创建的DataFrame而不是赋值给ret_df再返回
            # 为了符合您的要求，这里我们仍然使用ret_df作为变量名来接收结果，但直接返回它
            ret_df = pd.DataFrame(
                [
                    {
                        "工号": record.card_no,
                        "姓名": record.name,
                        "日期": record.date,
                        "上班状态": record.arrival_status,
                        "上班时间": record.arrival_time,
                        "下班状态": record.departure_status,
                        "下班时间": record.departure_time,
                    }
                    for record in attendance_summary
                ]
            )
            print("考勤分析结果：")
            print(ret_df.head())

            # 直接返回ret_df，它现在包含了考勤分析的结果
            return ret_df

        except Exception as e:
            print(f"处理过程中发生错误: {e}")
            return (
                ret_df.copy()
            )  # 出错时返回一个空的DataFrame副本（或直接用return pd.DataFrame()）
        finally:
            connection.close()
            print("\n数据库连接已关闭。")
    else:
        print("数据库连接失败，请检查连接信息和网络。")
        return (
            ret_df.copy()
        )  # 数据库连接失败时返回一个空的DataFrame副本（或直接用return pd.DataFrame()）


if __name__ in {"__main__", "__mp_main__"}:
    # 设置测试日期范围 (可以改为从命令行参数获取)
    start_date_str = "2025-05-12"
    end_date_str = "2025-05-12"
    fetch_and_analysis(start_date_str, end_date_str)
